package com.esiag.pdsteam.referencement.dao;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import com.esiag.pdsteam.referencement.annotation.DataField;
import com.esiag.pdsteam.referencement.annotation.DataTable;
import com.esiag.pdsteam.referencement.annotation.TableIdentificateur;



public abstract class AbstractDataBaseAccess{
	
	String absPath;
	private String path;
	private DataBase db;
	
	public AbstractDataBaseAccess() {
		getAbsolutePath();
		//System.out.println("jdbc:hsqldb:file:" + absPath + "data;");
		db = new DataBase(getDataSource());
		try {
			db.createDB(getScript(), absPath);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public abstract DataSource getDataSource();

	/**
	 * 
	 */
	public void getAbsolutePath() {
		path = new File(getClass().getResource("").getPath()).getParentFile().getParent();  
        String det = path.substring(0, 5);  
        if (det.equals("file:")) {  
            absPath = path.substring(5) + System.getProperty("file.separator") + "data" + System.getProperty("file.separator");  
        } else {  
            absPath = path + System.getProperty("file.separator") + "data" + System.getProperty("file.separator");
        }
	}
	/**
	 * @param script
	 * @throws IOException 
	 */
		protected abstract String getScript() throws IOException;
		
		
		public void insert(Object o) throws Exception{
			if(o==null) throw new NullPointerException();
			Class uneClass=o.getClass();
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			StringBuffer buffer=new StringBuffer("insert into " + tableName + " (");
			StringBuffer buffer2=new StringBuffer(" values(");
			boolean first=true;
			for(int i=0;i<f.length;i++)
			{
				DataField a2=f[i].getAnnotation(DataField.class);
				if(a2==null) continue;
				if(!first) {
					buffer.append(",");
					buffer2.append(",");
				}
				else first=false;
				buffer.append((a2.fieldName().isEmpty())?f[i].getName():a2.fieldName());
				buffer2.append("'");
				f[i].setAccessible(true);
				buffer2.append(f[i].get(o));
				f[i].setAccessible(false);
				buffer2.append("'");
			}
			buffer2.append(" )");
			buffer.append(" )");
			buffer.append(buffer2);
			//System.out.println(buffer.toString());
			db.execQuery(buffer.toString());
		}
		
		public void update(Object o) throws Exception{
			if(o==null) throw new NullPointerException();
			Class uneClass=o.getClass();
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			StringBuffer buffer=new StringBuffer("update " + tableName + " SET ");
			boolean first=true;
			Field F=null;
			for(int i=0;i<f.length;i++)
			{
				
				DataField a2=f[i].getAnnotation(DataField.class);
				if(a2==null) continue;
				if(!first) buffer.append(",");
				else first=false;
				
				TableIdentificateur a3=f[i].getAnnotation(TableIdentificateur.class);
				if(a3!=null) {
					F=f[i];
				}
				buffer.append((a2.fieldName().isEmpty())?f[i].getName():a2.fieldName());
				buffer.append("='");
				f[i].setAccessible(true);
				buffer.append(f[i].get(o));
				f[i].setAccessible(false);
				buffer.append("'");
			}
			if(F==null) throw new Exception("Identificateur Not Present");
			buffer.append(" WHERE ");
			DataField a4=F.getAnnotation(DataField.class);
			buffer.append((a4.fieldName().isEmpty())?F.getName():a4.fieldName());
			buffer.append("='");
			F.setAccessible(true);
			buffer.append(F.get(o));
			F.setAccessible(false);
			buffer.append("'");
			//System.out.println(buffer.toString());
			db.execQuery(buffer.toString());
		}
		
		public <T> T selectByID(Object id,Class<T> uneClass) throws Exception{
			
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			Field F=null;
			for(int i=0;i<f.length;i++)
			{
				DataField a2=f[i].getAnnotation(DataField.class);
				if(a2==null) continue;
				TableIdentificateur a3=f[i].getAnnotation(TableIdentificateur.class);
				if(a3!=null) {
					F=f[i];
				}
			}
			if(F==null) throw new Exception("Identificateur Not Present");
			T object=uneClass.newInstance();
			
			DataField a4=F.getAnnotation(DataField.class);
			String fieldName=(a4.fieldName().isEmpty())?F.getName():a4.fieldName();
			

			
			
			ResultSet rs=db.doQuery("select * from "+ tableName + " where " + fieldName + " = '"+id+"'");
			rs.next();
			
			for(int i=0;i<f.length;i++)
			{
				DataField a2=f[i].getAnnotation(DataField.class);
				if(a2==null) continue;
				String fieldN=(a2.fieldName().isEmpty())?f[i].getName():a2.fieldName();
				f[i].setAccessible(true);
				f[i].set(object,rs.getObject(fieldN));
				f[i].setAccessible(false);
			}
			
			return object;
		}
		
		public <T> Set<T> select(Class<T> uneClass,String keys[],Object values[]) throws Exception{
			
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			
			
			StringBuffer buffer=new StringBuffer("select * from "+tableName);
			if(keys!=null)
			{
			boolean first=true;
			for(int i=0;i<keys.length;i++)
			{
				if(first)
				{
					buffer.append(" where ");
					first=false;
				}else{
					buffer.append(" and ");
				}
				
				buffer.append(keys[i]);
				buffer.append(" = '");
				buffer.append(values[i]);
				buffer.append("'");
				
			}
			}
			Set<T> res=new HashSet<T>();
			// System.out.println(buffer.toString());
			ResultSet rs=db.doQuery(buffer.toString());
			while(rs.next())
			{
				T object=uneClass.newInstance();
				for(int i=0;i<f.length;i++)
				{
					DataField a2=f[i].getAnnotation(DataField.class);
					if(a2==null) continue;
					String fieldN=(a2.fieldName().isEmpty())?f[i].getName():a2.fieldName();
					f[i].setAccessible(true);
					try
					{
						f[i].set(object,rs.getObject(fieldN));
					}catch(Exception ee)
					{
						f[i].set(object,rs.getObject(fieldN).toString());
					}
					f[i].setAccessible(false);
				}
				res.add(object);
			}
			return res;
		}
		
		public <T> Set<T> selectAll(Class<T> uneClass) throws Exception{
			return select(uneClass, null, null);
		}
		
		public void deleteByID(Object id,Class uneClass) throws Exception{
			
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			Field F=null;
			for(int i=0;i<f.length;i++)
			{
				DataField a2=f[i].getAnnotation(DataField.class);
				if(a2==null) continue;
				TableIdentificateur a3=f[i].getAnnotation(TableIdentificateur.class);
				if(a3!=null) {
					F=f[i];
				}
			}
			if(F==null) throw new Exception("Identificateur Not Present");
			
			DataField a4=F.getAnnotation(DataField.class);
			String fieldName=(a4.fieldName().isEmpty())?F.getName():a4.fieldName();
			db.execQuery("delete from "+ tableName + " where " + fieldName + " = '"+id+"'");
		}
		
		public void delete(Class uneClass,String keys[],Object values[]) throws Exception{
			
			DataTable a=(DataTable) uneClass.getAnnotation(DataTable.class);
			if(a==null) throw new Exception("no Annotation Present");
			Field []f=uneClass.getDeclaredFields();
			String tableName=(a.tableName().isEmpty())?uneClass.getSimpleName():a.tableName();
			
			
			StringBuffer buffer=new StringBuffer("delete  from "+tableName);
			if(keys!=null)
			{
			boolean first=true;
			for(int i=0;i<keys.length;i++)
			{
				if(first)
				{
					buffer.append(" where ");
					first=false;
				}else{
					buffer.append(" and ");
				}
				
				buffer.append(keys[i]);
				buffer.append(" = '");
				buffer.append(values[i]);
				buffer.append("'");
				
			}
			}
			
			
			db.execQuery(buffer.toString());
			
		}
		
		public void deleteAll(Class uneClass) throws Exception{
			 delete(uneClass, null, null);
		}
		
		public void execute(String req) throws ClassNotFoundException, SQLException
		{
			db.execQuery(req);
		}
		
		public ResultSet executeRS(String req) throws ClassNotFoundException, SQLException
		{
			return db.doQuery(req);
		}

		public DataBase getDb() {
			return db;
		}
		
}
